package com.zenithsun.pic.picsubject.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.CriteriaSpecification;
import org.springframework.stereotype.Repository;

import com.syzg.common.dao.impl.BaseDAOHibernate;
import com.zenithsun.common.security.sql.SqlInjectionException;
import com.zenithsun.common.security.sql.SqlInjectionManager;
import com.zenithsun.pic.picsubject.dao.PicsubjectDAO;
import com.zenithsun.pic.picsubject.model.Picsubject;

@Repository("picsubjectDao")
public class PicsubjectDAOImpl extends BaseDAOHibernate<Picsubject> implements PicsubjectDAO {

	@Override
	protected Class<Picsubject> getEntityClass() {
		return Picsubject.class;
	}
	/**
	 * 分页查询
	 * @param pageNo 当前页
	 * @param limit 页行数
	 * @param pics
	 * @return
	 */
	@Override
	public List<HashMap<String, Object>> findPicsubjectList(int pageNo, int limit,
			Picsubject pics, String startDate,String endDate) {
		// TODO Auto-generated method stub
		int begin = (pageNo-1)*limit;
		String sql = "SELECT P.*,N.STATE,N.createTime,(SELECT COUNT(D.ID) FROM PICDETAIL D WHERE PIC_ID=P.ID) picNum from (SELECT P1.*,P2.thumbnailpath coverPath,P2.shooting_Time shootingtime,P2.artist artist FROM PICSUBJECT P1 LEFT JOIN PICDETAIL P2 ON  P1.cover_id=P2.ID) P LEFT JOIN (SELECT V.state,V.ID reviewId,V.busId,DATE_FORMAT(V.create_time, '%Y-%m-%d') createTime FROM REVIEW V WHERE V.BUS_MARK='Pic') N ON P.ID = N.BUSID";
		SQLQuery query;List<HashMap<String, Object>> list = new ArrayList<>();
		try {
			query = getSession().createSQLQuery(getConditionList(sql,pics,startDate,endDate)+" limit "+begin+","+limit);
			query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
			list = query.list();
		} catch (SqlInjectionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 获取总行数
	 * @param pics
	 * @return
	 */
	@Override
	public int getPicsubjectCount(Picsubject pics,String startDate, String endDate) {
		// TODO Auto-generated method stub
		String sql = "SELECT count(*) totalCount  from (SELECT P1.*,P2.path coverPath,P2.shooting_Time shootingtime,P2.artist artist FROM PICSUBJECT P1 LEFT JOIN PICDETAIL P2 ON  P1.cover_id=P2.ID) P LEFT JOIN (SELECT V.state,V.ID reviewId,V.busId,DATE_FORMAT(V.create_time, '%Y-%m-%d') createTime FROM REVIEW V WHERE V.BUS_MARK='Pic') N ON P.ID = N.BUSID";
		SQLQuery query;
		Long count = null;
		try {
			query = getSession().createSQLQuery(getConditionList(sql,pics,startDate,endDate));
			count=Long.parseLong(query.uniqueResult().toString());
		} catch (SqlInjectionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 
		return count.intValue();
	}
	
	private String getCondition(String sql,Picsubject searchCondition) throws SqlInjectionException{
		StringBuffer sb=new StringBuffer();
		sb.append(" WHERE 1=1 ");
		if(StringUtils.isNotBlank(searchCondition.getName())){
			SqlInjectionManager.validateSqlParam(searchCondition.getName());
			sb.append(" AND P.name like '%").append(searchCondition.getName()).append("%'");
		}
		if(StringUtils.isNotBlank(searchCondition.getCreateUserId())){
			sb.append(" AND P.create_userId ='").append(searchCondition.getCreateUserId()).append("'");
		}
		if(searchCondition.getAllowShare()==1){
			sb.append(" AND N.state=3 and P.allowshare=1");
		}
		if(sb.length()==0){
			return "";
		}else{
			sb.insert(0, sql);
		}
		sb.append(" ORDER BY CREATE_TIME DESC");
		return sb.toString();
	}
	
	private String getConditionList(String sql,Picsubject searchCondition,
			 String startDate,String endDate) throws SqlInjectionException{
		StringBuffer sb=new StringBuffer();
		sb.append(" WHERE 1=1 ");
		if(StringUtils.isNotBlank(searchCondition.getName())){
			SqlInjectionManager.validateSqlParam(searchCondition.getName());
			sb.append(" AND ( P.name like '%").append(searchCondition.getName()).append("%'");
			sb.append(" or P.remark like '%").append(searchCondition.getName()).append("%'");	
			sb.append(" or P.keyword like '%").append(searchCondition.getName()).append("%'");	
			sb.append(")");
			sb.append(" or id in( select pic_id from picdetail where remark like '%"+searchCondition.getName()+"%' or artist like '%"+searchCondition.getName()+"%')");
		}
		if(startDate!=null&&endDate!=null){
			//sb.append("AND create_time >='"+startDate+"' AND create_time <='"+endDate+"'");
			sb.append("AND shootingtime >='"+startDate+"' AND shootingtime <='"+endDate+"'");
		}
		if(StringUtils.isNotBlank(searchCondition.getCreateUserId())){
			sb.append(" AND P.create_userId ='").append(searchCondition.getCreateUserId()).append("'");
		}
		if(searchCondition.getAllowShare()==1){
			sb.append(" AND N.state=3 and P.allowshare=1");
		}
		if(sb.length()==0){
			return "";
		}else{
			sb.insert(0, sql);
		}
		sb.append(" ORDER BY shootingtime DESC ");
		return sb.toString();
	}
	
	@Override
	public int getSharePicsubjectCount(Picsubject pics,String startDate, String endDate) {
		String sql = "SELECT count(*) totalCount from (SELECT P1.*,P2.path coverPath,P2.shooting_Time shootingtime,P2.artist artist FROM PICSUBJECT P1 LEFT JOIN PICDETAIL P2 ON  P1.cover_id=P2.ID) P LEFT JOIN (SELECT V.state,V.ID reviewId,V.busId,DATE_FORMAT(V.create_time, '%Y-%m-%d') createTime FROM REVIEW V WHERE V.BUS_MARK='Pic') N ON P.ID = N.BUSID";
		SQLQuery query;
		Long count = null;
		try {
			query = getSession().createSQLQuery(getConditionList(sql,pics,startDate,endDate));
			count=Long.parseLong(query.uniqueResult().toString());
		} catch (SqlInjectionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 
		return count.intValue();
	}
	@Override
	public List<HashMap<String, Object>> findSharePicsubjectList(int pageNo,
			int limit, Picsubject pics,String startDate,String endDate) {
		// TODO Auto-generated method stub
		int begin = (pageNo-1)*limit;
		String sql = "SELECT P.*,N.STATE,N.createTime,(SELECT COUNT(D.ID) FROM PICDETAIL D WHERE PIC_ID=P.ID) picNum from (SELECT P1.*,P2.thumbnailpath coverPath,P2.shooting_Time shootingtime,P2.artist artist FROM PICSUBJECT P1 LEFT JOIN PICDETAIL P2 ON  P1.cover_id=P2.ID) P LEFT JOIN (SELECT V.state,V.ID reviewId,V.busId,DATE_FORMAT(V.create_time, '%Y-%m-%d') createTime FROM REVIEW V WHERE V.BUS_MARK='Pic') N ON P.ID = N.BUSID";
		SQLQuery query;List<HashMap<String, Object>> list = new ArrayList<>();
		try {
			query = getSession().createSQLQuery(getConditionList(sql,pics,startDate,endDate)+"limit "+begin+","+limit);
			query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
			list = query.list();
		} catch (SqlInjectionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
		
	}
	
}
